﻿/**
 * GoText Palm: A program that allows to send text messages over the internet.
 *  Copyright (C) 2008 Nicola 'Gawaine' Racco
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
using System;
using System.Data.SqlServerCe;
using System.Data.SqlTypes;
using System.Collections.Generic;
using System.Text;
using GoTextPalm.Core.Data;

namespace GoTextPalm.Core.Persistence
{
    public class DBService
    {
        public static bool ExistsService(String name)
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                cmd.CommandText = "SELECT COUNT(*) FROM Service WHERE name LIKE @name";
                cmd.Parameters.Add("@name", name);
                Int32 count = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Dispose();
                return count > 0;
            }
            catch (SqlCeException ex)
            {
                throw new DBException(ex);
            }
        }

        public static void PersistService(ref Service s)
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                cmd.CommandText = "INSERT INTO Service (name, url, needs, " +
                    "maxRecipiens, maxChars, type, maxMessages, mmType, " +
                    "charsToReplace, charsToCount, welcome) VALUES (@name, " +
                    "@url, @needs, @mr, @mc, @type, @mm, @mmt, @ctr, @ctc, @wcm)";
                cmd.Parameters.Add("@name", s.Name);
                cmd.Parameters.Add("@url", s.Url);
                cmd.Parameters.Add("@needs", s.Needs);
                cmd.Parameters.Add("@mr", s.MaxRecipiens);
                cmd.Parameters.Add("@mc", s.MaxChars);
                cmd.Parameters.Add("@type", (int)s.Type);
                cmd.Parameters.Add("@mm", s.MaxMessages);
                cmd.Parameters.Add("@mmt", (int)s.MMMode);
                if (s.CharsToReplace.Count > 0)
                {
                    StringBuilder sb = new StringBuilder();
                    Dictionary<char, string>.Enumerator en = s.CharsToReplace.GetEnumerator();
                    bool first = false;
                    while (!en.MoveNext())
                    {
                        if (!first)
                            first = true;
                        else
                            sb.Append("|");
                        sb.Append(en.Current.Key + "=" + en.Current.Value);
                    }
                    cmd.Parameters.Add("@ctr", sb.ToString());
                }
                else
                    cmd.Parameters.Add("@ctr", "");
                if (s.CharsToRecount.Count > 0)
                {
                    StringBuilder sb = new StringBuilder();
                    Dictionary<char, int>.Enumerator en = s.CharsToRecount.GetEnumerator();
                    bool first = false;
                    while (!en.MoveNext())
                    {
                        if (!first)
                            first = true;
                        else
                            sb.Append("|");
                        sb.Append(en.Current.Key + "=" + en.Current.Value);
                    }
                    cmd.Parameters.Add("@ctc", sb.ToString());
                }
                else
                    cmd.Parameters.Add("@ctc", "");
                cmd.Parameters.Add("@wcm", s.Welcome != null ? s.Welcome : "");
                cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                cmd.CommandText = "SELECT @@IDENTITY FROM Service";
                s.Id = Convert.ToInt32(cmd.ExecuteScalar());

                cmd.CommandText = "INSERT INTO ServiceStat (idService, date, mexSent) VALUES (@id, @date, @mexSent)";
                cmd.Parameters.Add("@id", s.Id);
                cmd.Parameters.Add("@date", DateTime.Now);
                cmd.Parameters.Add("@mexSent", "0");
                cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                cmd.CommandText = "INSERT INTO LastMessageSent (idService, saved) VALUES (@id, @saved)";
                cmd.Parameters.Add("@id", s.Id);
                cmd.Parameters.Add("@saved", false);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (SqlCeException ex)
            {
                throw new DBException(ex);
            }
        }

        private static Dictionary<Int32, Service> GetServiceManifests()
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                cmd.CommandText = "SELECT s.*, ss.date, ss.mexSent FROM Service s INNER " +
                        "JOIN ServiceStat ss ON s.id = ss.idService";
                SqlCeDataReader dr = cmd.ExecuteReader();
                Dictionary<Int32, Service> sl = new Dictionary<Int32, Service>();
                Service s;
                while (dr.Read())
                {
                    s = newService(dr);
                    s.Stat = new Service.ServiceStat();
                    s.Stat.Date = dr.GetDateTime(12);
                    s.Stat.Sent = dr.GetInt32(13);
                    sl.Add(s.Id.Value, s);
                }
                dr.Close();
                cmd.Dispose();
                return sl;
            }
            catch (SqlCeException ex)
            {
                throw new DBException(ex);
            }
        }

        private static Service newService(SqlCeDataReader dr)
        {
            try
            {
                Service s = new Service();
                s.Id = dr.GetInt32(0);
                s.Name = dr.GetString(1);
                s.Url = dr.GetString(2);
                s.Needs = (Service.ServiceNeeds)dr.GetInt32(3);
                s.MaxRecipiens = dr.GetInt32(4);
                s.MaxChars = dr.GetInt32(5);
                s.Type = (Service.ServiceType)dr.GetInt32(6);
                s.MaxMessages = dr.GetInt32(7);
                s.MMMode = (Service.MaxMessagesMode)dr.GetInt32(8);
                SqlString str = dr.GetSqlString(9);
                if (str.Value.Length > 0)
                {
                    s.CharsToReplace = new Dictionary<char, string>();
                    String[] vals = str.ToString().Split('|');
                    foreach (string val in vals)
                    {
                        String[] sv = val.Split('=');
                        s.CharsToReplace.Add(sv[0].ToCharArray()[0], sv[1]);
                    }
                }
                str = dr.GetSqlString(10);
                if (str.Value.Length > 0)
                {
                    s.CharsToRecount = new Dictionary<char, int>();
                    String[] vals = str.ToString().Split('|');
                    foreach (string val in vals)
                    {
                        String[] sv = val.Split('=');
                        s.CharsToRecount.Add(sv[0].ToCharArray()[0], Convert.ToInt32(sv[1]));
                    }
                }
                s.Welcome = dr.GetSqlString(11);
                return s;
            }
            catch (SqlCeException ex)
            {
                throw new DBException(ex);
            }
        }

        private static Dictionary<Int32, Service.ServiceConfig> GetServiceConfigs()
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                cmd.CommandText = "SELECT * FROM ServiceConfig";
                SqlCeDataReader dr = cmd.ExecuteReader();
                Dictionary<Int32, Service.ServiceConfig> sl = new Dictionary<Int32, Service.ServiceConfig>();
                while (dr.Read())
                {
                    Service.ServiceConfig sc = new Service.ServiceConfig();
                    sc.Username = dr.GetSqlString(1);
                    sc.Password = dr.GetSqlString(2);
                    sc.Nickname = dr.GetSqlString(3);
                    sl.Add(dr.GetInt32(0), sc);
                }
                dr.Close();
                cmd.Dispose();
                return sl;
            }
            catch (SqlCeException ex)
            {
                throw new DBException(ex);
            }
        }

        public static Service[] GetServices()
        {
            try
            {
                Dictionary<Int32, Service> svs = GetServiceManifests();
                Dictionary<Int32, Service.ServiceConfig> cfs = GetServiceConfigs();
                foreach (Int32 idx in svs.Keys)
                    if (cfs.ContainsKey(idx))
                        svs[idx].Config = cfs[idx];
                Service[] array = new Service[svs.Count];
                svs.Values.CopyTo(array, 0);
                Array.Sort(array);
                return array;
            }
            catch (SqlCeException ex)
            {
                throw new DBException(ex);
            }
        }

        public static void PersistServiceConfig(Service s)
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                cmd.CommandText = "SELECT COUNT(*) FROM ServiceConfig WHERE idService = @id";
                cmd.Parameters.Add("@id", s.Id);
                Int32 count = (Int32)cmd.ExecuteScalar();
                StringBuilder query = new StringBuilder();
                if (count == 0)
                {
                    query.Append("INSERT INTO ServiceConfig (idService");
                    StringBuilder result = new StringBuilder();
                    if (s.NeedsUsername)
                    {
                        query.Append(",username");
                        result.Append(",@username");
                    }
                    if (s.NeedsNickname)
                    {
                        query.Append(",nickname");
                        result.Append(",@nickname");
                    }
                    if (s.NeedsPassword)
                    {
                        query.Append(",password");
                        result.Append(",@password");
                    }
                    query.Append(") VALUES (@id");
                    query.Append(result.ToString());
                    query.Append(")");
                }
                else
                {
                    query.Append("UPDATE ServiceConfig SET ");
                    if (s.NeedsUsername)
                        query.Append("username = @username");
                    if (s.NeedsNickname)
                        query.Append((s.NeedsUsername ? "," : "") + "nickname = @nickname");
                    if (s.NeedsPassword)
                        query.Append((s.NeedsUsername || s.NeedsPassword ? "," : "") + "password = @password");
                    query.Append(" WHERE idService = @id");
                }
                cmd.CommandText = query.ToString();
                if (s.NeedsUsername)
                    cmd.Parameters.Add("@username", s.Config.Username);
                if (s.NeedsNickname)
                    cmd.Parameters.Add("@nickname", s.Config.Nickname);
                if (s.NeedsPassword)
                    cmd.Parameters.Add("@password", s.Config.Password);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (SqlCeException ex)
            {
                throw new DBException(ex);
            }
        }

        public static void DeleteService(Service s)
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                cmd.Parameters.Add("@id", s.Id);
                if (s.Config != null)
                {
                    cmd.CommandText = "DELETE FROM ServiceConfig WHERE idService = @id";
                    cmd.ExecuteNonQuery();
                }
                cmd.CommandText = "DELETE FROM MessagesSent WHERE idService = @id";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM ServiceStat WHERE idService = @id";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM Service WHERE id = @id";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM LastMessageSent WHERE idService = @id";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM RecipientsForMessagesSent WHERE idMessageSent = @idm";
                foreach (Message m in s.Messages)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@idm", m.ID);
                    cmd.ExecuteNonQuery();
                }
                cmd.Dispose();
            }
            catch (SqlCeException ex)
            {
                throw new DBException(ex);
            }
        }

        public static Service[] GetUsableServices()
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                cmd.CommandText = "SELECT s.*,sc.username,sc.password,sc.nickname,ss.date,ss.mexSent FROM " +
                        "Service s INNER JOIN ServiceConfig sc ON s.id = sc.idService " +
                        "INNER JOIN ServiceStat ss ON s.id = ss.idService";
                SqlCeCommand lms_cmd = DB.Instance.NewCommand();
                lms_cmd.CommandText = "SELECT idMessage,saved FROM LastMessageSent WHERE idService = @id";
                SqlCeDataReader dr = cmd.ExecuteReader();
                LinkedList<Service> sl = new LinkedList<Service>();
                Service s;
                while (dr.Read())
                {
                    s = newService(dr);
                    s.Config = new Service.ServiceConfig();
                    s.Config.Username = dr.GetSqlString(12);
                    s.Config.Password = dr.GetSqlString(13);
                    s.Config.Nickname = dr.GetSqlString(14);
                    s.Stat = new Service.ServiceStat();
                    s.Stat.Date = dr.GetDateTime(15);
                    s.Stat.Sent = dr.GetInt32(16);
                    s.Messages = DBMessage.GetMessages(s.Id.Value);

                    lms_cmd.Parameters.Add("@id", s.Id);
                    SqlCeDataReader dr2 = lms_cmd.ExecuteReader();
                    if (dr2.Read())
                    {
                        SqlInt32 idMessage = dr2.GetSqlInt32(0);
                        Message toRemove = null;
                        if (!idMessage.IsNull)
                            foreach (Message m in s.Messages)
                                if (m.ID == idMessage)
                                {
                                    bool saved = dr2.GetBoolean(1);
                                    s.LastMessage = new LastMessage(m, saved);
                                    if (!saved)
                                        toRemove = m;
                                    break;
                                }
                        if (toRemove != null)
                            s.Messages.Remove(toRemove);
                    }
                    lms_cmd.Parameters.Clear();
                    dr2.Dispose();

                    sl.AddLast(s);
                }
                dr.Dispose();
                lms_cmd.Dispose();
                cmd.Dispose();
                Service[] array = new Service[sl.Count];
                sl.CopyTo(array, 0);
                Array.Sort(array);
                return array;
            }
            catch (SqlCeException ex)
            {
                throw new DBException(ex);
            }
        }

        public static void UpdateServiceStat(Service s)
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                cmd.CommandText = "UPDATE ServiceStat SET date = @date, mexSent = @ms WHERE idService = @id";
                cmd.Parameters.Add("@date", s.Stat.Date);
                cmd.Parameters.Add("@ms", s.Stat.Sent);
                cmd.Parameters.Add("@id", s.Id);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (SqlCeException ex)
            {
                throw new DBException(ex);
            }
        }
    }
}
